package com.freshdirect.src.dao;

import com.freshdirect.src.model.User;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import javax.sql.DataSource;

public class UserDAOImpl implements UserDAO{
private int id,id2,id3,id4;
private String cnumber;
private DataSource dataSource ;

@Override
    public void setDataSource(String filename) {
        Properties props = new Properties();
        InputStream fis = null;
        MysqlDataSource mysqlDS = null;
        try {
            fis = getClass().getResourceAsStream("/"+filename+".properties");
            props.load(fis);
            mysqlDS = new MysqlDataSource();
            mysqlDS.setURL(props.getProperty("DB_URL"));
            mysqlDS.setUser(props.getProperty("DB_USERNAME"));
            mysqlDS.setPassword(props.getProperty("DB_PASSWORD"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        this.dataSource = mysqlDS;
    }

@Override
    public User userdetails(String log) {
    String query = "call userdetails(?);";
    User user=null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{   
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setString(1,log);
            rs = ps.executeQuery();
            if (rs.next()){
                user = new User();
                user.setId(rs.getInt(1));
                user.setUserName(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setFullName(rs.getString(4));
                user.setEmail(rs.getString(5));
                user.setaddress(rs.getString(6));
                user.setTown(rs.getString(7));
                user.setPostalCode(rs.getString(8));
                id2=rs.getInt(9);
                id3=rs.getInt(10);
                user.setcardnumber(rs.getString(11));
                user.setcardtype(rs.getInt(12));
                user.setholdername(rs.getString(13));
            }
            else{
                System.out.println("No user found ");
            }
           // String yeah = ps.getString(11);
            //user.setResult(ps.getString(11));
           
       }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }return user;
    }
    
@Override
    public void login(User user) {
        String query = "call signin(?,?);";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setString(1,user.getUserName());
            ps.setString(2,user.getPassword());
            rs = ps.executeQuery();
               if(rs.next()){
                user.setResult(rs.getString(1));
                user.setId(rs.getInt(2));
            }
            else
            user.setResult("Wrong Username or Password");   
           // String yeah = ps.getString(11);
            //user.setResult(ps.getString(11));
           
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
    
    
    }
    
    
    @Override
    public void save(User user) {
        String query = "call signup2(?,?,?,?,?,?,?,?,?,?);";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getFullName());
            ps.setString(4, user.getEmail());
            ps.setString(5, user.getaddress());
            ps.setString(6, user.getTown());
            ps.setString(7, user.getPostalCode());
            ps.setString(8, user.getcardnumber());
            ps.setInt(9, user.getcardtype());
            ps.setString(10, user.getholdername());
            /*getHoldersName*/   
            // ps.registerOutParameter(11,Types.VARCHAR);
           rs = ps.executeQuery();
            if(rs.next()){
                user.setResult(rs.getString(1));
            }
           // String yeah = ps.getString(11);
            //user.setResult(ps.getString(11));
           
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        

    }
    
    public void update(User user) {
        String query = "call updateU(?,?,?,?,?,?,?,?,?,?,?);";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //id=user.getId();
        try{
          
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setInt(1, user.getId());
            ps.setString(2, user.getUserName());
            ps.setString(3, user.getPassword());
            ps.setString(4, user.getFullName());
            ps.setString(5, user.getEmail());
            ps.setString(6, user.getaddress());
            ps.setString(7, user.getTown());
            ps.setString(8, user.getPostalCode());
            ps.setString(9, user.getcardnumber());
            ps.setInt(10, user.getcardtype());
            ps.setString(11, user.getholdername());
            /*getHoldersName*/   
            // ps.registerOutParameter(11,Types.VARCHAR);
           rs = ps.executeQuery();
            if(rs.next()){
            user.setResult(rs.getString(1));
            }
           // String yeah = ps.getString(11);
            //user.setResult(ps.getString(11));
           
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        

    }

    @Override
    public int checkCreditsLimit(int userId, int credits) {
        String query = "call checkcredit(?,?,?)";
        Connection con = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        int flag = 0;
        System.out.println(userId +"     "+ credits);
        try{
            con = dataSource.getConnection();
            cs = con.prepareCall(query);
            cs.setInt(1,userId);
            cs.setInt(2,credits);
            cs.registerOutParameter(3, java.sql.Types.INTEGER);
            cs.execute();
            flag = cs.getInt(3);
           
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                cs.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }

    @Override
    public void updateCreditsBalance(int userId, int credits) {
        String query = "call updatecredit(?,?)";
        Connection con = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        int flag = 0;
        try{
            con = dataSource.getConnection();
            cs = con.prepareCall(query);
            cs.setInt(1,userId);
            cs.setInt(2,credits);
            cs.execute();
           
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                cs.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<User> getAll() {
        
        List<User> users = new ArrayList<User>();
        String query = "call getallusers();";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{   
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            rs = ps.executeQuery();
            while(rs.next()){
                User user = new User();
                user.setId(rs.getInt(1));
                user.setUserName(rs.getString(2));
                user.setFullName(rs.getString(3));
                user.setEmail(rs.getString(4));
                user.setaddress(rs.getString(5));
                user.setTown(rs.getString(6));
                user.setPostalCode(rs.getString(7));
                user.setBalance(rs.getInt(8));
                users.add(user);
            }
           
       }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return users;
    }

    @Override
    public void updateCreditCard(int id,String cardnumber, String holdername, int cardtype) {
        String query = "call updatecreditcard(?,?,?,?);";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
          
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setInt(1, id);
            ps.setString(2, cardnumber);
            ps.setInt(3, cardtype);
            ps.setString(4, holdername);
           rs = ps.executeQuery();
           
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
    }
    

   
    
   
    
}
